﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using WCF_BI_Portal.GeneralLibs;
using WCF_BI_Portal.Lists;
using WCF_BI_Portal.Controller;
using System.Data;
using System.Data.SqlClient;
using WCF_BI_Portal.GeneralLibs;
using WCF_BI_Portal.Lists;
using WCF_BI_Portal.Controller;
using System.ServiceModel;

namespace WCF_BI_Portal.Controller
{
    class ReUseLibs
    {
        public static void ReIndexDB()
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = 1000 * 10;
                cmd.CommandText = string.Format("DECLARE @MyTable VARCHAR(255) \n" +
                                                "DECLARE myCursor CURSOR FOR \n" +
                                                "   SELECT table_name \n" +
                                                "   FROM information_schema.tables \n" +
                                                "   WHERE table_type = 'base table' \n" +
                                                "OPEN myCursor \n" +
                                                "FETCH NEXT FROM myCursor INTO @MyTable \n" +
                                                "WHILE @@FETCH_STATUS = 0 \n" +
                                                "BEGIN \n" +
                                                "   PRINT 'Reindexing Table:  ' + @MyTable \n" +
                                                "   DBCC DBREINDEX(@MyTable, '', 80) \n" +
                                                "   FETCH NEXT FROM myCursor INTO @MyTable \n" +
                                                "END \n" +
                                                "CLOSE myCursor \n" +
                                                "DEALLOCATE myCursor \n" +
                                                "EXEC sp_updatestats \n"
                                                );
                cmd.ExecuteNonQuery();
                Config.ThoiDiemReIndexGanNhat = new NullableDate(DateTime.Now);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open) con.Close();
            }
        }

        public static void ResetConnection(string dbName)
        {
            try
            {
                if (LocalSettings.SetDatabaseName != dbName || dbName == "PortalBI")
                {
                    if (dbName == "")
                    {
                        //var error = new CustomFaultMsg { Message = "Chưa chỉ rõ CSDL cần kết nối đến" };
                        throw new FaultException("Chưa chỉ rõ CSDL cần kết nối đến");
                    }
                    LocalSettings.SetDatabaseName = dbName;
                    if (dbName == "PortalBI")
                        DanhMucDoiTuong.ReloadAllDanhMuc(true);
                    else
                        DanhMucDoiTuong.ReloadAllDanhMuc();
                }
            }
            catch (Exception ex)
            {
                //var error = new CustomFaultMsg { Message = ex.Message };
                throw new FaultException(ex.Message);
            }
        }

        public static DataTable DoanhSoLaiLoTheoTungChiNhanh(SqlCommand cmd, string dbName,
            DateTime thoiDiemDau, DateTime thoiDiemCuoi, int numInterval)
        {
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();

            DateTime dtPointer = thoiDiemDau;
            Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

            Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");
            //ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime));

            Controller.DsWCFBI ds = new DsWCFBI();
            DataTable dt = ds.Tables["DoanhSoLaiLoTungChiNhanh"];
            DataRowCollection ac = dt.Rows;
            while (dtPointer <= thoiDiemCuoi)
            {
                DataRow row = dt.NewRow();
                row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointer.Day, dtPointer.Month, dtPointer.Year);
                decimal phatSinh = TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtPointer, dtPointer.AddDays(1));
                row["Series1Value"] = phatSinh / 1000000;

                row["Series2Name"] = string.Format("{0}/{1}/{2}", dtPointer.Day, dtPointer.Month, dtPointer.Year);
                cmd.CommandText = "SELECT SUM(Value5 - Value3) AS Lai\n" +
                                    "FROM KhoanMucChungTu \n" +
                                    "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                    "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                    "											FROM ChungTu \n" +
                                    "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                    "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                    "																	) \n" +
                                    "                                                   AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                    "											)\n" +
                                    "                   )";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", dtPointer);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtPointer.AddDays(1));
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    if (dr["Lai"] is decimal)
                        row["Series2Value"] = (decimal)dr["Lai"] / 1000000;
                    else
                    {
                        object abc = dr["Lai"];
                        string teyp = abc.GetType().ToString();
                        row["Series2Value"] = (decimal)0;
                    }
                }
                dr.Close();

                //row["Series2Value"] = TaiKhoan.TinhLai(cmd, thoiDiemDau, thoiDiemCuoi);

                dt.Rows.Add(row);
                dtPointer = dtPointer.AddDays((int)numInterval);
            }
            return dt;
        }

        public static DsWCFBI.DoanhSoLaiLoCacChiNhanhRow DoanhSoLaiLoTheoTungChiNhanh(SqlCommand cmd, DsWCFBI.DoanhSoLaiLoCacChiNhanhRow rowFormat, string dbName, DateTime thoiDiemDau, DateTime thoiDiemCuoi)
        {
            try
            {
                cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                cmd.ExecuteNonQuery();

                Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");
                //ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime));

                rowFormat["Series1Name"] = dbName;
                decimal phatSinh = TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, thoiDiemDau, thoiDiemCuoi);
                rowFormat["Series1Value"] = phatSinh / 1000000;
                rowFormat["Series2Name"] = dbName;

                rowFormat["Series2Value"] = TaiKhoan.TinhLai(cmd, thoiDiemDau, thoiDiemCuoi) / 1000000;

                return rowFormat;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
            }
        }

        public static decimal[] LoiNhuanVonVaDauTuTungChiNhanh(string strCon, DateTime thoiDiemDau, DateTime thoiDiemCuoi)
        {
            //Kiểm tra xem nếu xem từ đầu kỳ cuối thì phải tính cả tồn đầu kỳ còn các trường hợp khác thì chỉ tính phát sinh.
            bool xemTuDauKyCuoi = false;
            SqlConnection con = new SqlConnection(strCon);

            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                cmd.ExecuteNonQuery();

                DateTime dtPointer = thoiDiemDau;
                Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");
                //ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, thoiDiemDau, thoiDiemCuoi));
                decimal tongChi = NhatKyThuChi.TongChi(cmd, thoiDiemDau, thoiDiemCuoi);

                //Chi chia làm 5 loại.
                //Chi khác 811, 635
                DanhMucTaiKhoan dmTKChiKhac811_635 = DanhMucTaiKhoan.Filter("SoHieu IN ('811','635')");
                decimal tongChiKhac811_635 = NhatKyThuChi.TongChiTheoTaiKhoanDoiUng(cmd, thoiDiemDau, thoiDiemCuoi, dmTKChiKhac811_635);
                //Chi phí hoạt động và sản xuất 641 . 642
                Guid taiKhoanChiPhiBanHangID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("641").ID;
                Guid taiKhoanChiPhiQuanLyID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("642").ID;
                Guid taiKhoanLuongID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("334").ID;
                SoDuTaiKhoan soDuTaiKhoanChiPhiBanHang = new SoDuTaiKhoan();
                SoDuTaiKhoan soDuTaiKhoanChiPhiQuanLy = new SoDuTaiKhoan();
                SoDuTaiKhoan soDuTaiKhoanLuong = new SoDuTaiKhoan();
                if (xemTuDauKyCuoi)
                {
                    soDuTaiKhoanChiPhiBanHang = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanChiPhiBanHangID);
                    soDuTaiKhoanChiPhiQuanLy = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanChiPhiQuanLyID);
                    soDuTaiKhoanLuong = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanLuongID);
                }
                else
                {
                    soDuTaiKhoanChiPhiBanHang = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(thoiDiemDau, thoiDiemCuoi, taiKhoanChiPhiBanHangID);
                    soDuTaiKhoanChiPhiQuanLy = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(thoiDiemDau, thoiDiemCuoi, taiKhoanChiPhiQuanLyID);
                    soDuTaiKhoanLuong = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(thoiDiemDau, thoiDiemCuoi, taiKhoanLuongID);
                }

                decimal chiPhiHoatDong = soDuTaiKhoanChiPhiBanHang.DuNo + soDuTaiKhoanChiPhiQuanLy.DuNo + soDuTaiKhoanLuong.DuNo;

                Guid taiKhoanChiPhiSXID = (Guid)DanhMucTaiKhoan.Instance.GetBySoHieu("213").ID;
                SoDuTaiKhoan soDuTaiKhoanChiPhiSX = new SoDuTaiKhoan();
                if (xemTuDauKyCuoi)
                {
                    soDuTaiKhoanChiPhiSX = SoDuTaiKhoan.CalculateSoDuSauBuTru(thoiDiemCuoi, taiKhoanChiPhiSXID);
                }
                else
                {
                    soDuTaiKhoanChiPhiSX = SoDuTaiKhoan.CalculatePhatSinhTrongKySauBuTru(thoiDiemDau, thoiDiemCuoi, taiKhoanChiPhiSXID);
                }

                //Chi nguồn vốn 411
                //Nguồn vốn 411
                DanhMucTaiKhoan dmTKChiNguonVon411 = DanhMucTaiKhoan.Filter("SoHieu = '411'");
                decimal tongChiNguonVon411 = NhatKyThuChi.TongChiTheoTaiKhoanDoiUng(cmd, thoiDiemDau, thoiDiemCuoi, dmTKChiNguonVon411);

                //Chi phí còn lại là chi phí mua hàng
                decimal tongChiPhiMuaHang = tongChi - tongChiKhac811_635 - soDuTaiKhoanChiPhiBanHang.DuNo - soDuTaiKhoanChiPhiQuanLy.DuNo;

                decimal laiGop = 0;
                //Tính lãi
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBan, SUM(Value3) AS GiaVon, SUM(Value7 - Value3) AS Lai\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                        "																	) \n" +
                                        "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                        "											)\n" +
                                        "                   )\n" +
                                        " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", thoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", thoiDiemCuoi);

                decimal giaVon = 0;

                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["Lai"] != DBNull.Value)
                        {
                            laiGop = (decimal)dr["Lai"];
                            giaVon = (decimal)dr["GiaVon"];
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                decimal tongChiPhi = chiPhiHoatDong + soDuTaiKhoanChiPhiSX.DuNo + tongChiKhac811_635;
                decimal tongVonDauTu = giaVon + tongChiPhi;

                return new decimal[] { laiGop, tongVonDauTu };
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static List<decimal> CalculatorCongNoSauBuTru(SqlCommand cmd, DateTime dtNgayKetThuc)
        {
            try
            {
                List<decimal> result = new List<decimal>();
                cmd.CommandTimeout = Config.TimeOut;
                Guid taiKhoanPhaiThuID = DanhMucTaiKhoan.Instance.GetBySoHieu("131").ID;
                Guid taiKhoanPhaiTraID = DanhMucTaiKhoan.Instance.GetBySoHieu("331").ID;
                decimal phaiThuSauBuTru = 0, phaiTraSauBuTru = 0;
                int count = 0;

                foreach (DoiTuongNghiepVu dtNV in DanhMucDoiTuongNghiepVu.GetDanhMucDoiTac())
                {
                    SoDuTheoDoiTuong noPhaiThu = SoDuTheoDoiTuong.CalculateFromDB(cmd, dtNgayKetThuc, taiKhoanPhaiThuID, dtNV.ID);
                    SoDuTheoDoiTuong noPhaiTra = SoDuTheoDoiTuong.CalculateFromDB(cmd, dtNgayKetThuc, taiKhoanPhaiTraID, dtNV.ID);
                    decimal phaiThu = noPhaiThu.DuNo - noPhaiThu.DuCo;
                    decimal phaiTra = noPhaiTra.DuCo - noPhaiTra.DuNo;
                    decimal soDu = phaiThu - phaiTra;
                    if (Math.Abs(soDu) > 0)
                    {
                        if (phaiThu >= phaiTra)
                        {
                            phaiThuSauBuTru += phaiThu - phaiTra;
                        }
                        else
                        {
                            phaiTraSauBuTru += phaiTra - phaiThu;
                        }
                    }
                }
                result.Add(phaiThuSauBuTru);
                result.Add(phaiTraSauBuTru);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
        }

        //Giá vốn còn lại = Đầu kỳ + tông nhập - tổng xuất
        public static decimal CalculateGiaVonUocTinhConTrongKho(DateTime thoiDiemXem)
        {
            SqlConnection con = new SqlConnection(LocalSettings.ConnectString);
            try
            {
                con.Open();
                DateTime now = DateTime.Now;
                ThoiDiemKhoaSo thoiDiemKhoaSoGanNhat = DanhMucThoiDiemKhoaSo.Instance.GetThoiDiemChotSoDuGanNhat(thoiDiemXem);
                string sql = string.Format("DECLARE @VonDauKy money \n" +
                                            "DECLARE @VonNhap money \n" +
                                            "DECLARE @VonXuat money \n" +
                                            "SELECT @VonDauKy = SUM(CONVERT(decimal ,SoLuong) * CONVERT(decimal ,DonGia)) FROM TonKho WHERE ThoiDiemKhoaSoID = '{0}' \n" +
                                            "SELECT @VonNhap = SUM(CONVERT(decimal ,SoLuong) * CONVERT(decimal ,DonGiaQD)) FROM NhatKyNhapKho WHERE @ThoiDiemDau <  Ngay AND Ngay < @ThoiDiemCuoi \n" +
                                            "SELECT @VonXuat = SUM(CONVERT(decimal ,SoLuong) * CONVERT(decimal ,DonGiaQD)) FROM NhatKyXuatKho WHERE @ThoiDiemDau <  Ngay AND Ngay < @ThoiDiemCuoi \n" +
                                            "IF(@VonDauKy IS NULL) SET @VonDauKy=0 \n" +
                                            "IF(@VonNhap IS NULL) SET @VonNhap=0 \n" +
                                            "IF(@VonXuat IS NULL) SET @VonXuat=0 \n" +
                                            "SELECT @VonDauKy + @VonNhap - @VonXuat Result",
                                            thoiDiemKhoaSoGanNhat.ID);

                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("ThoiDiemDau", SqlDbType.DateTime));
                cmd.Parameters["ThoiDiemDau"].Value = thoiDiemKhoaSoGanNhat.ThoiDiem;
                cmd.Parameters.Add(new SqlParameter("ThoiDiemCuoi", SqlDbType.DateTime));
                cmd.Parameters["ThoiDiemCuoi"].Value = thoiDiemXem;

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                dt = dt.DefaultView.ToTable();
                if (dt.Rows.Count > 0 && dt.Rows[0]["Result"] is decimal)
                    return (decimal)dt.Rows[0]["Result"];
                else return (decimal)0;
            }
            catch (Exception ex)
            {
                return (decimal)0;
            }
        }
    }
}
